from demo.init import db
from sqlalchemy import and_
'''
操作数据库，增删改查
'''

class Kids(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255), nullable=False, unique=True)
    age = db.Column(db.Integer, nullable=False)

    def __repr__(self):
        return '<Kids name:%r age:%r>' % (self.name, self.age)

# 查询所有的kid
def queryAllKid():
    kids = Kids.query.all()
    print(kids)

# 添加数据到数据库
def add_data():
    kid1 = Kids(name='kid1', age=4)
    kid2 = Kids(name='kid2', age=5)
    kid3 = Kids(name='kid3', age=9)
    kid4 = Kids(name='kid4', age=12)
    kid5 = Kids(name='kid5', age=12)
    kid6 = Kids(name='kid6', age=12)

    db.session.add_all([kid1, kid2, kid3, kid4, kid5, kid6])
    db.session.commit()

# 创建数据库表
db.create_all()

# 删除Kids表所有行
Kids.query.delete()

# ----------- 增 -------------
# 添加一条数据
kid1 = Kids(name='kid1', age=4)
db.session.add(kid1)
db.session.commit()

# 添加多条数据(添加的是数组)
kid2 = Kids(name='kid2', age=5)
kid3 = Kids(name='kid3', age=9)
kid4 = Kids(name='kid4', age=12)

db.session.add_all([kid2, kid3, kid4])
db.session.commit()

queryAllKid()

# ----------- 删 -------------

# 先查询后删除
kid_del_1 = Kids.query.filter_by(name='kid1').first()
if kid_del_1 != None:
    db.session.delete(kid_del_1)
    db.session.commit()
queryAllKid()

# 删除Kids表所有行
Kids.query.delete()
queryAllKid()

# ----------- 改 -------------
add_data()
# 修改，先查询后修改
kid_update_1 = Kids.query.filter_by(name='kid1').first()
if kid_update_1 != None:
    kid_update_1.name = 'kid_1_update'
    db.session.commit()
queryAllKid()

# ----------- 查 -------------

# 原生slq语句查询,待补充
sql = 'select * from kids'
result_1 = db.session.execute(sql)

# 查询全部
result_2 = Kids.query.all()
print('查询全部：', result_2)

# 主键查询
result_3 = Kids.query.get(1)
print('主键查询：', result_3)

# 单条件查询
result_4 = Kids.query.filter_by(name='kid2').first()
print('条件查询：', result_4)

# 多条件查询
result_5 = Kids.query.filter(and_(Kids.name == 'kid2', Kids.age == 5)).first()
print('多条件查询：', result_5)

# 比较查询 .__lt__(5):小于5，__le__(5):小于等于5，__gt__(5):大于5，__ge__(5):大于等于5
result_6 = Kids.query.filter(Kids.age.__gt__(5)).all()
print('比较查询：', result_6)

# in查询
result_7 = Kids.query.filter(Kids.name.in_(['kid1', 'kid2', 'kid3'])).all()
print('in查询：', result_7)

# 排序
result_8 = Kids.query.order_by('age').all()
print('排序:', result_8)

# 限制查询，在符合age=12的结果中，跳过一个，剩下的结果选取前2个
result_9 = Kids.query.filter_by(age=12).offset(1).limit(2).all()
print('限制查询：', result_9)
